Imports System.IO
Imports System.Data.SqlClient
Imports System.Runtime.InteropServices
Imports System.Text
Imports System.Collections

''' -----------------------------------------------------------------------------
''' Project	 : BizBlox
''' Class	 : BizBlox.DataAccess
''' 
''' -----------------------------------------------------------------------------
''' <summary>
''' Provide data access layer for BizBlox Data Object
''' </summary>
''' <remarks>
''' </remarks>
''' <history>
''' 	[cinj]	16/06/2006	Created
''' </history>
''' -----------------------------------------------------------------------------
Public Class DataAccess
    Private m_strConnection As String

    'key to the application is from the webconfig
    Public Sub New()
        m_strConnection = DSNManager.GetConnectionString()
    End Sub 'New

    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Run raw SQL using current BizBlox connection properties
    ''' </summary>
    ''' <param name="strSQL"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' 	[cinj]	21/03/2005	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Sub RunSQL(ByVal strSQL As String)

        Console.WriteLine(strSQL)

        Dim MyCommand As IDbCommand
        Dim MyConnection As IDbConnection
        Try
            MyCommand = ProviderFactory.GetCommand(strSQL, CommandType.Text)
            MyConnection = ProviderFactory.GetConnection(m_strConnection)
            MyCommand.Connection = MyConnection
            MyCommand.ExecuteNonQuery()
        Catch x As Exception
            If Not MyConnection Is Nothing Then MyConnection.Close()
            If Not MyCommand Is Nothing Then MyCommand.Dispose()
            Throw New DOException("Save failed.", strSQL, x)
        Finally
            If Not MyConnection Is Nothing Then MyConnection.Close()
            If Not MyCommand Is Nothing Then MyCommand.Dispose()
        End Try
    End Sub

    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Run raw SQL using current BizBlox connection properties.
    ''' </summary>
    ''' <param name="strSQL"></param>
    ''' <returns>Data set of results</returns>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' 	[cinj]	21/03/2005	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Function RunSQLReturnDataSet(ByVal strSQL As String) As DataSet
        Try
            Dim ds As New DataSet("DataSet")
            Dim da As IDbDataAdapter
            da = ProviderFactory.GetAdapter(strSQL, m_strConnection)
            da.Fill(ds)
            Return ds
        Catch ex As SqlException
            Throw New DOException("Find/Load failed.", strSQL, ex)
        End Try
    End Function

    Friend Sub AddSQLToTransaction(ByVal query As String, ByVal trans As IDbTransaction, ByVal myConnection As IDbConnection)
        Dim MyCommand As IDbCommand
        Try
            MyCommand = ProviderFactory.GetCommand()
            MyCommand.Connection = myConnection
            MyCommand.CommandText = query
            MyCommand.Transaction = trans
            MyCommand.ExecuteNonQuery()
        Catch ex As Exception
            If Not MyCommand Is Nothing Then
                Try
                    MyCommand.Transaction.Rollback()
                    Throw New DOTransactionException("Update in transaction failed.", ex)
                Catch ex2 As Exception
                    Throw New DOTransactionRollbackException("Rollback failed after " & ex2.ToString(), ex)
                End Try
            End If
            Throw ex
        Finally
            If Not MyCommand Is Nothing Then MyCommand.Dispose()
        End Try
    End Sub



    'DEPRICATED!
    Friend Sub RunSQLTransaction(ByVal Queries As ArrayList, Optional ByVal Isolation As Integer = CInt(IsolationLevel.ReadCommitted))
        Dim myConnection As IDbConnection
        Dim MyCommand As IDbCommand
        'debug output

        Try
            myConnection = ProviderFactory.GetConnection(m_strConnection)
            MyCommand = ProviderFactory.GetCommand()
            MyCommand.Connection = myConnection
            MyCommand.Transaction = myConnection.BeginTransaction(Isolation)

            Dim sql As String
            Dim myEnumerator As System.Collections.IEnumerator = Queries.GetEnumerator()
            While myEnumerator.MoveNext()
                If Not myEnumerator.Current Is Nothing Then
                    sql = CStr(myEnumerator.Current)
                    MyCommand.CommandText = sql
                    MyCommand.ExecuteNonQuery()
                End If
            End While
            MyCommand.Transaction.Commit()
        Catch nx As InvalidOperationException
            Throw New DOException("Unable to Perform Commit.", ComposeSqlStatements(Queries), nx)
        Catch x As Exception
            Try
                MyCommand.Transaction.Rollback()
            Catch ex As Exception
                Throw New DOException("Unable to Perform Rollback.", ComposeSqlStatements(Queries), ex)
            End Try
            Throw New DOTransactionException("Commit failed.", ComposeSqlStatements(Queries), x)
        Finally
            If Not myConnection Is Nothing Then myConnection.Close()
            If Not MyCommand.Transaction Is Nothing Then MyCommand.Transaction.Dispose()
            If Not MyCommand Is Nothing Then MyCommand.Dispose()
        End Try
    End Sub

    Private Function ComposeSqlStatements(ByVal src As ArrayList) As String
        Dim myEnumerator As System.Collections.IEnumerator = src.GetEnumerator()
        Dim compositeSql As New StringBuilder
        While myEnumerator.MoveNext()
            If Not myEnumerator.Current Is Nothing Then
                compositeSql = compositeSql.Append(CStr(myEnumerator.Current)).Append(vbCrLf)
            End If
        End While
        Return compositeSql.ToString()
    End Function

End Class

